--- exec SP_Search_Tape_Reel_Requests null, null,'Issued_pulled', null, null select * from TAPE_REEL_REQUEST_LOT_TABLE
ALTER Proc SP_Search_Tape_Reel_Requests( @TR_ProcessStep_id int, @Location_id int, @Status varchar(50), @FROM_DATE VARCHAR(10), @TO_DATE VARCHAR(10))
as
begin
	DECLARE @SQLString NVARCHAR(3000)
	DECLARE @ParamDefinition NVARCHAR(500)

	SET @ParamDefinition = N'@Prm_TR_Processstep_ID VARCHAR(50), @Prm_Location VARCHAR(50), @Prm_Status VARCHAR(50)'	
	SET @SQLString = N'SELECT Work_Order_NO, P.part_number, L.L_Vendor_Name, TR.Tape_Reel_Request_Id, Req_Qty, CONVERT( CHAR(10), Req_Date, 101 ) AS Req_Date, TR.Status, ( select SUM(Qty_Pulled)-SUM(TR_Qty)-SUM(Return_Qty)-SUM(Reject_Qty) from TAPE_REEL_REQUEST_LOT_TABLE trl where Tape_Reel_Request_Id = TR.Tape_Reel_Request_Id ) as Buy_In, ( select sum(Qty_Pulled) from TAPE_REEL_REQUEST_LOT_TABLE where Tape_Reel_Request_Id = TR.Tape_Reel_Request_Id ) as QtyPulled  from Tape_Reel_Request_Table TR, Product P, LOcation L WHERE P.ProcessStep_ID = TR.tr_ProcessStep_Id and L.Location_id = TR.tr_Location_id'

	IF @TR_ProcessStep_id > 0
	BEGIN
		SET @SQLString = @SQLString + ' AND tr_PROCESSSTEP_ID = @Prm_TR_Processstep_ID'
	END

	
	IF @Location_id > 0
	BEGIN
		SET @SQLString = @SQLString + ' AND tr_location_id = @Prm_Location'
	END
	if @Status <> '' and @Status = 'Issued_pulled'
	begin		
		SET @SQLString = @SQLString + ' AND Status in( ''Issued'', ''Pulled'' )'
	end

	IF @Status <> '' and @Status != 'Issued_pulled'
	BEGIN
		SET @SQLString = @SQLString + ' AND Status in( @Prm_Status )'
	END
	IF ( @FROM_DATE IS NOT NULL AND @FROM_DATE != '' )
    	BEGIN
		SET @SQLString = @SQLString + ' AND Req_Date >= ''' + @FROM_DATE + ''''
	END

	IF ( @TO_DATE IS NOT NULL AND @TO_DATE != '' )
	BEGIN
		SET @SQLString = @SQLString + ' AND Req_Date <= ''' + @TO_DATE + ''''
	END

	SET @SQLString = @SQLString + ' ORDER BY CONVERT(INT, WORK_ORDER_NO) DESC'

	print @SQLString

	EXECUTE sp_executesql @SQLString, @ParamDefinition, @Prm_Tr_Processstep_ID = @TR_ProcessStep_id, @Prm_Location = @Location_id, @Prm_Status = @Status 

end